IF OBJECT_ID('dbo.GetFunds') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetFunds
    IF OBJECT_ID('dbo.GetFunds') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetFunds >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetFunds >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetFunds
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns list of Funds from Insight
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetFunds()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/26/07     KC        Created
-- 8/15/07     KC        Permissions
--
-- =======================================================================
CREATE PROCEDURE dbo.GetFunds
          (@Sort         VARCHAR(5)='Nbr') 
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     FundId              INT
     FundNumber          VARCHAR
     FundName            VARCHAR
     FundNbrName         VARCHAR        -- concatenate number-name just in case 
                                        -- easier to search
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT
               
     -- Result Query  
     IF @Sort != 'Nbr'
          SELECT DISTINCT Id AS FundId, 
                    FundNumber,
                    FundShortTitle  AS FundName,
                    FundNumber + '-' + FundShortTitle AS FundNbrName
          FROM      dbo.AgrFund
          WHERE     FundShortTitle IS NOT NULL
          ORDER BY FundShortTitle
     ELSE
          SELECT DISTINCT Id AS FundId, 
                    FundNumber,
                    FundShortTitle  AS FundName,
                    FundNumber + '-' + FundShortTitle AS FundNbrName
          FROM      dbo.AgrFund
          WHERE     FundShortTitle IS NOT NULL
          ORDER BY FundNumber
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetFunds') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetFunds >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetFunds >>>'
go

GRANT EXECUTE ON dbo.GetFunds TO InsightReport,Insight
go
